<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class NoAttend extends AdminController
{

    function __construct()
    {
        parent::__construct();
        $this->load->model("Attend_model");
        $this -> load -> model("Admin_model");
        $this -> load ->library('ProjectService/admin_service');
//      $this -> sidebar = "Account-Admin";
    }

    public function Index($teacher_name=false)
    {
        $data=array();
        if($teacher_name){
            $data['teacher_name']=urldecode($teacher_name);
        }
        $connar['items']='distinct(dept_name)';
        $connar['where']=$this->sqlEscape("(role_id=?? or find_in_set(??,sub_role_id))",array(WebRole::Teacher[XPHEnum::Value],WebRole::Teacher[XPHEnum::Value]));
        $dept_name=$this->Admin_model->get_list_full($connar);

        $data['dept_name']= $dept_name;

        //获取教师属性
        $teachersAttribute = array();
        foreach ((new TeachersAttribute())->getAll() as $k => $v) {
                $teachersAttribute[$k]['Value'] = $v[XPHEnum::Value];
                $teachersAttribute[$k]['Caption'] = $v[XPHEnum::Caption];
        }
        $data['teachersAttribute'] = $teachersAttribute;

        $this->load->view('admin/noattendList',$data);
    }


    function getDatas($params = array(), $isExport = false){
         $connar = array();
         //查询当前用户的角色
        $manage=$this->admin_service->getAdminById(getSess()['id']);

          $sub_role=explode(',', getSess()['sub_role_id']);
         array_push($sub_role, getSess()['role_id']);
             
         $where = '';
         //管理员可查询全校
        if(in_array(WebRole::Manage[XPHEnum::Value], $sub_role)){
            $where = 'and 1=1';
        }else if(in_array(WebRole::UnitSupervisorGroupLeader[XPHEnum::Value], $sub_role)){            
        //督导组长可查询本分院
           $where =" and dept_name = '".$manage['dept_name']."'";
        }else{
            // 其余人查自己
            $where=" and id=".getSess()['id'];
        }

        $where.=" and (FIND_IN_SET(".WebRole::ExternalSupervisor[XPHEnum::Value].", sub_role_id)=0 or sub_role_id=''or sub_role_id is null) ";
        if(isset($_POST['dept_name']) && $_POST['dept_name']!='-1'){
            $where.=" and dept_name= '".$_POST['dept_name']."'";
        }
        $where1='';
        if(isset($_POST['start_time']) && $_POST['start_time']!='' ){
            $where1.=' and db_attend.listen_time>='.strtotime($_POST['start_time']);
        }
        if(isset($_POST['end_time'])  && $_POST['end_time']!=''){
            $where1.=' and db_attend.listen_time<='.strtotime($_POST['end_time'].'23:59:59');
        }
        if(isset($_POST['username']) && $_POST['username']!=''){
            $where.=$this->sqlEscape(" and username=??" ,array($_POST['username']));
        }

        //根据听课教师身份筛选
        if(isset($_POST['type'])&&$_POST['type']>-1){
            if($_POST['type'] == NoAttendType::Supervisor[XPHEnum::Value]){//未被督导听课
             $where1.= $this->sqlEscape(" and (type=?? or type =?? or type=??)",array(AttendType::School[XPHEnum::Value],AttendType::Unit[XPHEnum::Value],AttendType::Outside[XPHEnum::Value]));
            }else{//未被教师听课
                 $where1.= $this->sqlEscape(" and type=??",array(AttendType::Teacher[XPHEnum::Value]));
            }
          }

        //教师属性
        if(isset($_POST['teachersAttribute']) && $_POST['teachersAttribute']!='-1'){
            $where.=" and teachers_attribute= '".$_POST['teachersAttribute']."'";
        }

        $where .= $this->sqlEscape(" and status=??" ,array(CommonStatus::UnDeleted[XPHEnum::Value]));    

        if (!$isExport) {
            $params = $_POST;
            $connar = $this->Attend_model->pickPages($connar, $params);
             $pageSize=$params['pageSize'];
             $pageNo=$params['pageNo'];
             // $sql ="select db_admin.id,usercode,username,db_admin.dept_name from db_admin left join db_attend on db_attend.teacher_code=db_admin.usercode ".$where1." where usercode not in (select a.usercode from db_admin a,db_attend b where a.usercode=b.teacher_code and a.usercode=b.teacher_code and b.status=".AttendStatus::Submit[XPHEnum::Value].") and (role_id = ".WebRole::Teacher[XPHEnum::Value]." or find_in_set(".WebRole::Teacher[XPHEnum::Value].",db_admin.sub_role_id)) ". $where.$where1." limit ".$pageSize* ($pageNo-1) .','.$pageSize;
            $sql="select db_admin.id, usercode, username, db_admin.dept_name,db_admin.teachers_attribute FROM db_admin WHERE usercode NOT IN (SELECT db_admin.usercode FROM db_attend LEFT JOIN db_admin ON db_admin.usercode = db_attend.teacher_code WHERE (role_id = ".WebRole::Teacher[XPHEnum::Value]." OR FIND_IN_SET(".WebRole::Teacher[XPHEnum::Value].", sub_role_id) ) and db_attend.status=".AttendStatus::Submit[XPHEnum::Value].$where1." GROUP BY usercode )".$where." and (role_id = ".WebRole::Teacher[XPHEnum::Value]." OR FIND_IN_SET(".WebRole::Teacher[XPHEnum::Value].", sub_role_id) ) GROUP BY usercode ORDER BY id"." limit ".$pageSize* ($pageNo-1) .','.$pageSize;
             // $sql1 ="select count(*) as count from db_admin where usercode not in (select a.usercode from db_admin a,db_attend b where a.usercode=b.teacher_code and a.usercode=b.teacher_code and b.status=".AttendStatus::Submit[XPHEnum::Value].") and (role_id = ".WebRole::Teacher[XPHEnum::Value]." or find_in_set(".WebRole::Teacher[XPHEnum::Value].",db_admin.sub_role_id))".$where;         
            $sql1="select count(*) count from (select db_admin.id, usercode, username, db_admin.dept_name,db_admin.teachers_attribute FROM db_admin WHERE usercode NOT IN (SELECT db_admin.usercode FROM db_attend LEFT JOIN db_admin ON db_admin.usercode = db_attend.teacher_code WHERE (role_id = ".WebRole::Teacher[XPHEnum::Value]." OR FIND_IN_SET(".WebRole::Teacher[XPHEnum::Value].", sub_role_id) ) and db_attend.status=".AttendStatus::Submit[XPHEnum::Value].$where1." GROUP BY usercode )".$where."  and (role_id = ".WebRole::Teacher[XPHEnum::Value]." OR FIND_IN_SET(".WebRole::Teacher[XPHEnum::Value].", sub_role_id) ) GROUP BY usercode ORDER BY id)no";
        }else{
             // $sql ="select id,usercode,username,dept_name from db_admin where usercode not in (select a.usercode from db_admin a,db_attend b where a.usercode=b.teacher_code and a.usercode=b.teacher_code and b.status=".AttendStatus::Submit[XPHEnum::Value].") and (role_id = ".WebRole::Teacher[XPHEnum::Value]." or find_in_set(".WebRole::Teacher[XPHEnum::Value].",db_admin.sub_role_id))".$where;
            $sql="select db_admin.id, usercode, username, db_admin.dept_name,db_admin.teachers_attribute FROM db_admin WHERE usercode NOT IN (SELECT db_admin.usercode FROM db_attend LEFT JOIN db_admin ON db_admin.usercode = db_attend.teacher_code WHERE (role_id = ".WebRole::Teacher[XPHEnum::Value]." OR FIND_IN_SET(".WebRole::Teacher[XPHEnum::Value].", sub_role_id) ) and db_attend.status=".AttendStatus::Submit[XPHEnum::Value].$where1." GROUP BY usercode )".$where." and (role_id = ".WebRole::Teacher[XPHEnum::Value]." OR FIND_IN_SET(".WebRole::Teacher[XPHEnum::Value].", sub_role_id) ) GROUP BY usercode ORDER BY id";
        }

        $data["ret"] = $this->Admin_model->query($sql);
        foreach ($data["ret"] as $key => $value) {
           $data["ret"][$key]['teachers_attribute'] =  $value['teachers_attribute'] ? (new TeachersAttribute())->show($value['teachers_attribute']) : '无';
        }

        if (!$isExport) {
            $count=$this->Admin_model->query($sql1);
            $data['count']=$count[0]['count'];
            $data['pageNo']=$pageNo;
            $data['totalPage']=ceil($data["count"] / $pageSize);
            echo json_encode($data);
        } else {
           $this-> load ->library('PHPExcel/PHPExcel');
            $excel = new PHPExcel();
            //公共样式
            $excel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(11); 
            $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setWrapText(true);
            $excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(23);
            $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $excel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

            $x_line=0;
            $y_line=1;

            //正式开始
            $excel = new PHPExcel();
            $excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);

              //标题
            $excel->getActiveSheet()->mergeCells(PHPExcel_Cell::stringFromColumnIndex(0).'1:'.PHPExcel_Cell::stringFromColumnIndex(2).'1');
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(0).'1','未听课教师');
            $excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
            $excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);
            $excel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $i=2;
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(0).$i,'姓名');
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(1).$i,'教工号');
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(2).$i,'部门');
              
        $i++;
        foreach ($data['ret'] as $k => $v) {
           
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(0).$i,$v['username']);
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(1).$i,$v['usercode']);
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(2).$i,$v['dept_name']);          
            $i++;
        }
        //设置对齐
        $excel->getActiveSheet()->getStyle('A1:'.PHPExcel_Cell::stringFromColumnIndex(9).($i-1))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $excel->getActiveSheet()->getStyle('A1:'.PHPExcel_Cell::stringFromColumnIndex(0).($i-1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


            $write = new PHPExcel_Writer_Excel2007($excel); 
            $director = "upload/export/" . date('Ymd', time()) . "/";
            if (!file_exists($director)) {
                //检查是否有该文件夹，如果没有就创建，并给予最高权限
                mkdir($director, 0777);
            }        
            $file_name ='督导未听课汇总'.date('Ymd').'.xlsx';           
            $write->save($director.iconv('utf-8', 'gb2312', $file_name));
            exit(retJson('返回地址',true,$director.$file_name));
        }

    }

}